﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using System.Reflection;
using System.IO;
using Aspose.Cells;
using SharpCompress.Reader;
using SharpCompress.Common;

namespace ZFine.Code
{
    /// <summary>
    /// 导出Excel
    /// </summary>
    public class AsposeExcel
    {

        private string outFileName = "";
        private string fullFilename = "";
        private Workbook book = null;
        private Worksheet sheet = null;


        private AsposeExcel(string outfilename, string tempfilename) //导出构造数
        {
            outFileName = outfilename;
            book = new Workbook();
            // book.Open(tempfilename);这里我们暂时不用模板
            sheet = book.Worksheets[0];
        }

        private AsposeExcel(string fullfilename) //导入构造数
        {
            fullFilename = fullfilename;
            // book = new Workbook();
            //book.Open(tempfilename);
            //sheet = book.Worksheets[0];
        }

        private AsposeExcel() //导入构造数
        {
            //fullFilename = fullfilename;
            // book = new Workbook();
            //book.Open(tempfilename);
            //sheet = book.Worksheets[0];
        }

        private void AddTitle(string title, int columnCount)
        {
            sheet.Cells.Merge(0, 0, 1, columnCount);
            sheet.Cells.Merge(1, 0, 1, columnCount);

            Cell cell1 = sheet.Cells[0, 0];
            Style Style = new Aspose.Cells.Style();
            cell1.PutValue(title);
            Style.HorizontalAlignment = TextAlignmentType.Center;
            Style.Font.Name = "黑体";
            Style.Font.Size = 14;
            Style.Font.IsBold = true;

            Cell cell2 = sheet.Cells[1, 0];
            cell1.PutValue("查询时间：" + DateTime.Now.ToLocalTime());
            cell2.SetStyle(Style);
        }

        private void AddHeader(DataTable dt)
        {
            Cell cell = null;
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                cell = sheet.Cells[0, col];
                cell.PutValue(dt.Columns[col].ColumnName);
                Style Style = new Aspose.Cells.Style();
                Style.Font.IsBold = true;
                cell.SetStyle(Style);
            }
        }

        private void AddBody(DataTable dt)
        {
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    sheet.Cells[r, c].PutValue(dt.Rows[r][c].ToString());
                }
            }
        }



        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private Boolean DatatableToExcel(DataTable dt)
        {
            Boolean yn = false;
            try
            {
                //sheet.Name = sheetName;

                //AddTitle(title, dt.Columns.Count);
                //AddHeader(dt);
                AddBody(dt);

                sheet.AutoFitColumns();
                //sheet.AutoFitRows();

                book.Save(outFileName);
                //excel.Save(HttpUtility.UrlEncode(“材料设备表.xls”), SaveType.OpenInBrowser , FileFormatType.Default, response);
                yn = true;
                return yn;
            }
            catch (Exception e)
            {
                return yn;
                // throw e;
            }
        }



        /// <summary>
        /// 导入   陈尚岩
        /// </summary>
        /// <param name="table">查询的表名（默认为第一个表，当表名是数字类型，则从0开始按位置寻找）</param>
        /// <param name="isno">第一行是否为列名（默认为是）</param>
        /// <returns></returns>
        private DataTable ExcelToDatatalbe(string table = "", bool isno = true)
        {
            if (table == "")
            {
                table = "0";
            }
            int index = 0;
            Workbook book = new Workbook();
            book.Open(fullFilename);
            Worksheet sheet;
            if (int.TryParse(table, out index))
            {
                sheet = book.Worksheets[index];
            }
            else
            {
                sheet = book.Worksheets[table];
            }
            Cells cells = sheet.Cells;
            //获取excel中的数据保存到一个datatable中
            ///出口出第一个单元格的行数。 出口的第一个单元格的列号。 要输入的行数。要导入的列数。指示是否在第一行的数据是出口到DataTable中的列的名称
            DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1,
                isno);
            // dt_Import.
            return dt_Import;
        }


        /// <summary>
        /// 生成文件流  陈尚岩
        /// </summary>
        /// <param name="dt">表</param>
        /// <param name="Mytitle">标题</param>
        /// <param name="Mytitle">位置</param>
        /// <returns></returns>
        private System.IO.MemoryStream FileFlow(DataTable dt, string[] Mytitle = null, int[] MyIndex = null)
        {
            //初始化
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            if (MyIndex == null)
            {
                MyIndex = new int[dt.Columns.Count];
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    MyIndex[c] = c;
                }
            }
            if (Mytitle != null)
            {
                //标题存在  添加样式
                Style Style = new Aspose.Cells.Style();
                Style.HorizontalAlignment = TextAlignmentType.Center; //居中
                Style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //设置颜色
                Style.Pattern = BackgroundType.Solid;
                Style.Font.Name = "黑体"; //字体
                Style.Font.Size = 14; //大小
                Style.Font.IsBold = true; //加粗
                for (int i = 0; i < Mytitle.Length; i++)
                {
                    //是大标题 单独设置
                    if (Mytitle.Length == 1)
                    {
                        //给当前标题 添加样式
                        sheet.Cells.Merge(0, 0, 1, dt.Columns.Count);
                        Cell cell1 = sheet.Cells[0, 0];
                        cell1.PutValue(Mytitle[i]);
                        cell1.SetStyle(Style);

                        //cell1.SetRowHeight(0, 38);//设置行高
                        //cell1.SetColumnWidth(1, 20);//设置列宽
                    }
                    else
                    {
                        Cell cell1 = sheet.Cells[0, i];
                        cell1.PutValue(Mytitle[i]); //添加标题
                        cell1.SetStyle(Style);
                    }
                }
                //给第一行之后添加数据
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    var index = 0; //当前位置
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {

                        if (MyIndex.Contains(c))
                        {
                            sheet.Cells[r + 1, c - index].PutValue(dt.Rows[r][c].ToString()); //添加内容
                        }
                        else
                        {
                            index = index + 1; //不存在 位置+1
                        }
                    }
                }
            }
            else
            {
                //没有标题 直接添加数据
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    var index = 0;
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        if (MyIndex.Contains(c))
                        {
                            sheet.Cells[r, c - index].PutValue(dt.Rows[r][c].ToString());
                        }
                        else
                        {
                            index = index + 1; //不存在 位置+1
                        }
                    }
                }

            }
            //生成文件流 或者数据流
            sheet.AutoFitColumns();
            System.IO.MemoryStream ms = book.SaveToStream();
            return ms;
        }










        /// <summary>
        /// 导出Excel到本地upload下 true 成功 陈尚岩--没有完善是全部导出
        /// </summary>
        /// <param name="dt">要保存的数据</param>
        /// <param name="FileName">表名</param>
        /// <returns></returns>
        public static bool Export(DataTable dt, string FileName)
        {

            FileName = System.Web.HttpContext.Current.Server.MapPath("/Upload/" + FileName); //文件路径
            AsposeExcel tt = new AsposeExcel(FileName, ""); //不用模板, saveFileDialog1是什么？上面已经说过  
            return tt.DatatableToExcel(dt);
        }


        /// <summary>
        /// 导出Excel到本地upload下 true 成功 陈尚岩--没有完善是全部导出
        /// </summary>
        /// <param name="MyList">要保存的数据</param>
        /// <param name="FileName">表名</param>
        /// <returns></returns>
        public static bool Export<T>(List<T> MyList, string FileName)
        {
            DataTable dt = ListToDataTable<T>(MyList); //转换
            FileName = System.Web.HttpContext.Current.Server.MapPath("/Upload/" + FileName); //文件路径
            AsposeExcel tt = new AsposeExcel(FileName, ""); //不用模板, saveFileDialog1是什么？上面已经说过  
            return tt.DatatableToExcel(dt);
        }


        public static bool SaveFileaddress(DataTable dt, string fileName, string[] Mytitle = null, int[] MyIndex = null)
        {
            var ret = true;
            try
            {
                AsposeExcel tt = new AsposeExcel();
                System.IO.MemoryStream ms = tt.FileFlow(dt, Mytitle, MyIndex);
                byte[] bt = ms.ToArray();
                ms.Write(bt,0,bt.Length);
                fileName = System.Web.HttpContext.Current.Server.MapPath("/Upload/" + fileName); //文件路径
             
            }
            catch (Exception)
            {
                ret = false;
            }
            return ret;
        }
       

        /// <summary>
        /// 导出Excel 直接下载  陈尚岩--可设置标题和显示位置
        /// </summary>
        /// <param name="dt">要导出的表</param>
        /// <param name="fileName">导出的文件名</param>
        /// <param name="Mytitle">是否有标题</param>
        /// /// <param name="Mytitle">显示位置</param>
        /// <returns></returns>
        public static bool SaveFile(DataTable dt, string fileName, string[] Mytitle = null, int[] MyIndex = null)
        {
            var ret = true;
            try
            {
                AsposeExcel tt = new AsposeExcel();
                System.IO.MemoryStream ms = tt.FileFlow(dt, Mytitle, MyIndex);
                byte[] bt = ms.ToArray();
                System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                //通知浏览器下载文件而不是打开
                //  Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));

                //解决浏览器中文编码问题
                if (System.Web.HttpContext.Current.Request.UserAgent.ToLower().IndexOf("msie") > -1)
                {
                    fileName = System.Web.HttpUtility.UrlPathEncode(fileName);
                }
                if (System.Web.HttpContext.Current.Request.UserAgent.ToLower().IndexOf("firefox") > -1)
                {
                    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
                }
                else
                {
                    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
                }
                int lengtn = bt.Length;
                System.Web.HttpContext.Current.Response.BinaryWrite(bt);
                System.Web.HttpContext.Current.Response.Flush();
                System.Web.HttpContext.Current.Response.End();
            }
            catch {
                ret = false;
            }
            return ret;
        }

        /// <summary>
        /// 导出Excel 直接下载 陈尚岩--可设置标题和显示位置
        /// </summary>
        /// <param name="MyList">要导出的表</param>
        /// <param name="fileName">导出的文件名</param>
        /// <param name="Mytitle">标题名</param>
        /// <param name="Mytitle">显示位置</param>
        /// <returns></returns>
        public static bool SaveFile<T>(List<T> MyList, string fileName, string[] Mytitle = null, int[] MyIndex = null)
        {
            var ret = true;
            try
            {
                DataTable dt = ListToDataTable<T>(MyList);//转换
                AsposeExcel tt = new AsposeExcel();
                System.IO.MemoryStream ms = tt.FileFlow(dt, Mytitle, MyIndex);
                byte[] bt = ms.ToArray();
                System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

                //通知浏览器下载文件而不是打开
                //  Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));

                //解决浏览器中文编码问题
                if (System.Web.HttpContext.Current.Request.UserAgent.ToLower().IndexOf("msie") > -1)
                {
                    fileName = System.Web.HttpUtility.UrlPathEncode(fileName);
                }
                if (System.Web.HttpContext.Current.Request.UserAgent.ToLower().IndexOf("firefox") > -1)
                {
                    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
                }
                else
                {
                    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
                }

                System.Web.HttpContext.Current.Response.BinaryWrite(bt);

                System.Web.HttpContext.Current.Response.Flush();
                System.Web.HttpContext.Current.Response.End();
            }
            catch {
                ret = false;
            }
            return ret;
        }




        /// <summary>
        /// 导入 返回DataTable
        /// </summary>
        /// <param name="FileName">文件路径</param>
        /// <param name="sino">是否删除原文件</param>
        /// <returns></returns>
        public static DataTable Import(string FileName, bool sino = false)
        {
            //string Path = System.Web.HttpContext.Current.Server.MapPath(FileName);//文件路径
            DataTable dt = null;//返回值
            try
            {
                AsposeExcel tt = new AsposeExcel(FileName);
                dt = tt.ExcelToDatatalbe();//转换成表
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (sino)
                {
                    System.IO.File.Delete(FileName); //删除本地文件
                }
            }
            return dt;
        }

        /// <summary>
        /// 导入 返回List
        /// </summary>
        /// <param name="FileName">文件路径</param>
        /// <param name="sino">是否删除原文件</param>
        /// <returns></returns>
        public static IList<T> ImportList<T>(string FileName, bool sino = false)
        {
            string Path = System.Web.HttpContext.Current.Server.MapPath(FileName);//文件路径
            DataTable dt = null;//返回值
            try
            {
                AsposeExcel tt = new AsposeExcel(Path);
                dt = tt.ExcelToDatatalbe();//转换成表
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (sino)
                {
                    System.IO.File.Delete(Path); //删除本地文件
                }
            }
            IList<T> MyList = DataSetToIList<T>(dt);
            return MyList;
        }



        /// <summary> 
        /// DataSet装换为泛型集合 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="p_DataSet">DataSet</param> 
        /// <param name="p_TableIndex">待转换数据表索引</param> 
        /// <returns></returns> 
        /// 2008-08-01 22:46 HPDV2806 
        public static IList<T> DataSetToIList<T>(DataTable p_Data, int p_TableIndex = 0)
        {

            // 返回值初始化 
            IList<T> result = new List<T>();
            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值 
                        if (pi.Name.Equals(p_Data.Columns[i].ColumnName))
                        {
                            // 数据库NULL值单独处理 
                            if (p_Data.Rows[j][i] != DBNull.Value)
                                pi.SetValue(_t, p_Data.Rows[j][i], null);
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                result.Add(_t);
            }
            return result;
        }


        /// <summary>
        /// 将泛类型集合List类转换成DataTable
        /// </summary>
        /// <param name="list">泛类型集合</param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(List<T> entitys)
        {
            //检查实体集合不能为空
            if (entitys == null || entitys.Count < 1)
            {
                throw new Exception("需转换的集合为空");
            }
            //取出第一个实体的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            DataTable dt = new DataTable();
            for (int i = 0; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }
            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //检查所有的的实体都为同一类型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要转换的集合元素类型不一致");
                }
                object[] entityValues = new object[entityProperties.Length];
                for (int i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);
                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }





        /// <summary>
        /// 解压当前文件
        /// </summary>
        /// <param name="fime"></param>
        /// <returns></returns>
        public static bool GetRar(string FileName, string PathName)
        {
            var ret = false;
            try
            {
                using (Stream stream = File.OpenRead(FileName))
                {
                    var reader = ReaderFactory.Open(stream);
                    while (reader.MoveToNextEntry())
                    {
                        if (!reader.Entry.IsDirectory)
                        {
                            Console.WriteLine(reader.Entry.FilePath);
                            reader.WriteEntryToDirectory(PathName, ExtractOptions.ExtractFullPath | ExtractOptions.Overwrite);
                        }
                    }
                }
                ret = true;
            }
            catch
            {
            }
            finally
            {
                System.IO.File.Delete(FileName); //删除本地文件
            }
            return ret;

        }
        

    }
}